package com.atguigu.flow.demo;


import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.atguigu.flow.comm.ExcelConst;
import com.atguigu.flow.entity.*;
import com.atguigu.flow.entity.DTO.Productphoto;
import com.atguigu.flow.entity.vo.*;
import com.atguigu.flow.mapper.*;
import com.atguigu.flow.service.ItemsService;
import com.atguigu.flow.service.IwInstanceService;
import com.atguigu.flow.service.QuotationsLineService;
import com.atguigu.flow.utils.*;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.sun.org.apache.xerces.internal.impl.dv.util.Base64;
import jdk.nashorn.api.scripting.ScriptObjectMirror;
import lombok.extern.slf4j.Slf4j;
import org.assertj.core.util.Strings;
import org.dom4j.*;
import org.dom4j.io.SAXReader;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.mybatis.spring.annotation.MapperScan;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.core.io.ClassPathResource;
import org.springframework.core.io.Resource;
import org.springframework.test.context.junit4.SpringRunner;
import org.springframework.util.StringUtils;
import sun.misc.BASE64Decoder;
import sun.misc.BASE64Encoder;

import javax.script.ScriptEngine;
import javax.script.ScriptEngineManager;
import javax.script.ScriptException;
import java.io.*;
import java.nio.charset.StandardCharsets;
import java.text.ParseException;
import java.time.LocalDate;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;


/**
 * @author: kw
 * @description: 签名连通测试
 * @create: 2021/11/18 15:16
 */
@RunWith(SpringRunner.class)
@SpringBootTest
@Slf4j
@MapperScan("com.atguigu.flow.mapper")

public
class excel_export {



    @Autowired
    SqlMapper sqlMapper;

    @Autowired
    private IwInstanceMapper instanceMapper;
    @Autowired
    private IwTaskMapper iwTaskMapper;

    @Autowired
    private
    IwInstanceService iwInstanceService;
    @Autowired
    private
    Workflow workflow;
    @Autowired
    private SysRoleMapper sysRoleMapper;

    @Autowired
    private ItemsMapper itemsMapper;
    @Autowired
    private ItemsQuoteMapper itemsQuoteMapper;




    @Autowired
    private SysAttachmentMapper sysAttachmentMapper;
    @Autowired
    private AttachmentSave attachmentSave;
    @Autowired
    GetFlowModule_field_util getFlowModule_field_util;
    @Autowired
    private ShipmentApplyMapper shipmentApplyMapper;
    @Autowired
    private
    ItemsService itemsService;
    @Autowired
    QuotationsLineService quotationsLineService;

    @Autowired
    private  XmlTest xmlTest;


    private static final Logger logapp = LoggerFactory.getLogger("applog");

    @Test
    public void export_hscodeamount() throws Exception{
        String path = "/Apidoc/hscode.txt"; // 去掉末尾的分号
        Resource resource = new ClassPathResource(path);

        List<String> lines = new ArrayList<>();

        try (BufferedReader reader = new BufferedReader(
                new InputStreamReader(resource.getInputStream()))) {
            String line;
            while ((line = reader.readLine()) != null) {
                lines.add(line);
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        List<HscodeShipments> hscodeShipmentsList =new ArrayList<>();
        for (int i=0;i<=lines.size()-1;i++){
            List<HscodeShipments> hscodeShipments=quotationsLineService.gethscode_amount(lines.get(i));
            if (hscodeShipments.size()>0)
                hscodeShipmentsList.addAll(hscodeShipments);

        }
        //System.out.println(hscodeShipmentsList);
        List<JSONObject> headList = new ArrayList<>();


        List<List<String>> lists = new ArrayList<>();
        JSONObject jsonObject = new JSONObject();

        jsonObject = new JSONObject();
        jsonObject.put(ExcelConst.TITLE, "海关编码");
        jsonObject.put(ExcelConst.WIDTH, 13*256);
        jsonObject.put(ExcelConst.TYPE, "1");
        headList.add(jsonObject);


        jsonObject = new JSONObject();
        jsonObject.put(ExcelConst.TITLE, "事业部");
        jsonObject.put(ExcelConst.WIDTH, 13*256);
        jsonObject.put(ExcelConst.TYPE, "1");
        headList.add(jsonObject);

        jsonObject = new JSONObject();
        jsonObject.put(ExcelConst.TITLE, "核算号");
        jsonObject.put(ExcelConst.WIDTH, 13*256);
        jsonObject.put(ExcelConst.TYPE, "1");
        headList.add(jsonObject);

        jsonObject = new JSONObject();
        jsonObject.put(ExcelConst.TITLE, "货号");
        jsonObject.put(ExcelConst.WIDTH, 15*256);
        jsonObject.put(ExcelConst.TYPE, "1");
        headList.add(jsonObject);


        jsonObject = new JSONObject();
        jsonObject.put(ExcelConst.TITLE, "商品名称");
        jsonObject.put(ExcelConst.WIDTH, 21*256);
        jsonObject.put(ExcelConst.TYPE, "1");
        headList.add(jsonObject);

        jsonObject = new JSONObject();
        jsonObject.put(ExcelConst.TITLE, "在手订单$");
        jsonObject.put(ExcelConst.WIDTH, 11*256);
        jsonObject.put(ExcelConst.TYPE, "2");
        jsonObject.put(ExcelConst.FORMAT, "#,#0");

        // jsonObject.put(ExcelConst.TYPE, "1");
        //jsonObject.put(ExcelConst.FORMAT, "");
        headList.add(jsonObject);
        jsonObject = new JSONObject();
        jsonObject.put(ExcelConst.TITLE, "以往一年出货金额$");
        jsonObject.put(ExcelConst.WIDTH, 15*256);
        jsonObject.put(ExcelConst.TYPE, "2");
        jsonObject.put(ExcelConst.FORMAT, "#,#0.00");
        headList.add(jsonObject);

        jsonObject = new JSONObject();
        jsonObject.put(ExcelConst.TITLE, "图片");
        jsonObject.put(ExcelConst.WIDTH, 13*256);
        jsonObject.put(ExcelConst.TYPE, "1");
        headList.add(jsonObject);



        for (int i = 0; i < hscodeShipmentsList.size(); i++) {
            //if (StringUtils.isEmpty(dataList.get(i).getPhoto())) continue;
            List<String> list = new ArrayList<>();



            list.add(hscodeShipmentsList.get(i).getHscode());
            list.add(hscodeShipmentsList.get(i).getBuname());
            list.add(hscodeShipmentsList.get(i).getGpcode());
            list.add(hscodeShipmentsList.get(i).getItemno());
            list.add(hscodeShipmentsList.get(i).getCHNItemName());

            //list.add(dataList.get(i).getENGItemName());
            list.add(String.valueOf(hscodeShipmentsList.get(i).getUnshipmentsamout()));
            list.add(String.valueOf(hscodeShipmentsList.get(i).getCustomsAccount()));



            BASE64Encoder base64Encoder = new BASE64Encoder();
            if (!StringUtils.isEmpty(hscodeShipmentsList.get(i).getPhoto()))
                list.add( base64Encoder.encode(hscodeShipmentsList.get(i).getPhoto()));
            else
                list.add( base64Encoder.encode("empty".getBytes()));
            lists.add(list);
        }
        //System.out.println(lists);

        ExcelUtil.getExcel("d:\\出口退税变更.xlsx", lists, headList,true);




        // 打印读取的字节列表（以十六进制形式）

    }

    @Test
    public void test_item_sql() throws IOException {
//        String fromrq="2022-01-01";
//        String torq="2022-12-31";
        //List<QuotationsLine>  dataList=quotationsLineService.getsell_well_items(fromrq,torq);
        //System.out.println(dataList);
        BASE64Encoder base64Encoder = new BASE64Encoder();
        String str=base64Encoder.encode("empty".getBytes());
        System.out.println(str);
        BASE64Decoder base64Decoder = new BASE64Decoder();
        byte[] imageData = base64Decoder.decodeBuffer(str);
        String utf_8 = new String(imageData, StandardCharsets.UTF_8);
        System.out.println(utf_8);



    }
    @Test
    public void texst_excelbs() throws  Exception{

    }

   @Test
   public void purchaseorderlist_export() throws  Exception {
       LocalDate ld = LocalDate.now();
       String today = ld.toString();
       LocalDate lastDate = ld.minusYears(1);
       String yesterday = lastDate.toString();
       String PurchaseOrderNo = "";
       String province="山东省";
       List<Purchaseorderslist> dataList = quotationsLineService.getpurchaseorderslist(yesterday, today, PurchaseOrderNo,province,"");
       logapp.info(Strings.formatIfArgs("共有记录数%d",dataList.size()));
       List<JSONObject> headList = new ArrayList<>();


       List<List<String>> lists = new ArrayList<>();
       JSONObject jsonObject = new JSONObject();
       jsonObject.put(ExcelConst.TITLE, "图片");
       jsonObject.put(ExcelConst.WIDTH, 13*256);
       jsonObject.put(ExcelConst.TYPE, "1");
       headList.add(jsonObject);

       jsonObject = new JSONObject();
       jsonObject.put(ExcelConst.TITLE, "事业部");
       jsonObject.put(ExcelConst.WIDTH, 13*256);
       jsonObject.put(ExcelConst.TYPE, "1");
       headList.add(jsonObject);

       jsonObject = new JSONObject();
       jsonObject.put(ExcelConst.TITLE, "采购合同号");
       jsonObject.put(ExcelConst.WIDTH, 13*256);
       jsonObject.put(ExcelConst.TYPE, "1");
       headList.add(jsonObject);

       jsonObject = new JSONObject();
       jsonObject.put(ExcelConst.TITLE, "采购币种");
       jsonObject.put(ExcelConst.WIDTH, 13*256);
       jsonObject.put(ExcelConst.TYPE, "1");
       headList.add(jsonObject);

       jsonObject = new JSONObject();
       jsonObject.put(ExcelConst.TITLE, "合同日期");
       jsonObject.put(ExcelConst.WIDTH, 13*256);
       jsonObject.put(ExcelConst.TYPE, "1");
       headList.add(jsonObject);

       jsonObject = new JSONObject();
       jsonObject.put(ExcelConst.TITLE, "厂商名称");
       jsonObject.put(ExcelConst.WIDTH, 21*256);
       jsonObject.put(ExcelConst.TYPE, "1");
       headList.add(jsonObject);

       jsonObject = new JSONObject();
       jsonObject.put(ExcelConst.TITLE, "公司货号");
       jsonObject.put(ExcelConst.WIDTH, 13*256);
       jsonObject.put(ExcelConst.TYPE, "1");
       headList.add(jsonObject);

       jsonObject = new JSONObject();
       jsonObject.put(ExcelConst.TITLE, "客户货号");
       jsonObject.put(ExcelConst.WIDTH, 13*256);
       jsonObject.put(ExcelConst.TYPE, "1");
       headList.add(jsonObject);


       jsonObject = new JSONObject();
       jsonObject.put(ExcelConst.TITLE, "中文品名");
       jsonObject.put(ExcelConst.WIDTH, 21*256);
       jsonObject.put(ExcelConst.TYPE, "1");
       headList.add(jsonObject);

       jsonObject = new JSONObject();
       jsonObject.put(ExcelConst.TITLE, "合同数量");
       jsonObject.put(ExcelConst.WIDTH, 11*256);
       jsonObject.put(ExcelConst.TYPE, "2");
       jsonObject.put(ExcelConst.FORMAT, "0");

       // jsonObject.put(ExcelConst.TYPE, "1");
       //jsonObject.put(ExcelConst.FORMAT, "");
       headList.add(jsonObject);
       jsonObject = new JSONObject();
       jsonObject.put(ExcelConst.TITLE, "采购单价");
       jsonObject.put(ExcelConst.WIDTH, 15*256);
       jsonObject.put(ExcelConst.TYPE, "2");
       jsonObject.put(ExcelConst.FORMAT, "0.00");
       headList.add(jsonObject);



       jsonObject = new JSONObject();
       jsonObject.put(ExcelConst.TITLE, "采购总价");
       jsonObject.put(ExcelConst.WIDTH, 15*256);
       jsonObject.put(ExcelConst.TYPE, "2");
       jsonObject.put(ExcelConst.FORMAT, "0.00");
       headList.add(jsonObject);

       jsonObject = new JSONObject();
       jsonObject.put(ExcelConst.TITLE, "省份");
       jsonObject.put(ExcelConst.WIDTH, 21*256);
       jsonObject.put(ExcelConst.TYPE, "1");
       headList.add(jsonObject);

       jsonObject = new JSONObject();
       jsonObject.put(ExcelConst.TITLE, "城市");
       jsonObject.put(ExcelConst.WIDTH, 21*256);
       jsonObject.put(ExcelConst.TYPE, "1");
       headList.add(jsonObject);
       if (dataList.size()>0) {
           for (int i = 0; i < dataList.size(); i++) {
               //if (StringUtils.isEmpty(dataList.get(i).getPhoto())) continue;
               List<String> list = new ArrayList<>();


               BASE64Encoder base64Encoder = new BASE64Encoder();
               if (!StringUtils.isEmpty(dataList.get(i).getPhoto()))
                   list.add(base64Encoder.encode(dataList.get(i).getPhoto()));
               else
                   list.add(base64Encoder.encode("empty".getBytes()));
//           private String buname;
//           private String PurchaseCurrency;//采购币种
//
//           private String PurchaseOrderNo;
//           private Date orderdate;
//           private String SupplierCorpName;//工厂名称
//           private String ItemNo;//公司货号
//           private String customeritemno;//客户货号
//           private String CHNItemName;//产品名称
//           private Double  OrderQty; //合同数量
//           private Double  PurchasePrice; //采购单价
//           private Double  PurchaseAmount; //采购


               list.add(dataList.get(i).getBuname());
               list.add(dataList.get(i).getPurchaseOrderNo());
               list.add(dataList.get(i).getPurchaseCurrency());
               list.add(DateUtil.formatDate(dataList.get(i).getOrderdate()));
               list.add(dataList.get(i).getSupplierCorpName());
               list.add(dataList.get(i).getItemNo());
               list.add(dataList.get(i).getCustomeritemno());
               list.add(dataList.get(i).getCHNItemName());

               //list.add(dataList.get(i).getENGItemName());
               list.add(String.valueOf(dataList.get(i).getOrderQty()));
               list.add(String.valueOf(dataList.get(i).getPurchasePrice()));
               list.add(String.valueOf(dataList.get(i).getPurchaseAmount()));
               list.add(dataList.get(i).getProvince());
               list.add(dataList.get(i).getCity());

               lists.add(list);
           }
           //System.out.println(lists);

           ExcelUtil.getExcel("d:\\采购合同产品列表.xlsx", lists, headList, true);

       }
   }
    @Test
    public void grass_export() throws  Exception{
        String fromrq="2023-01-01";
        String torq="2023-6-13";
        String supplierno="";
        List<GlassShipments>  dataList=quotationsLineService.grass_ship_find(fromrq,torq,"");
        System.out.println(dataList);
        List<JSONObject> headList = new ArrayList<>();


        List<List<String>> lists = new ArrayList<>();
        JSONObject jsonObject = new JSONObject();
        jsonObject.put(ExcelConst.TITLE, "图片");
        jsonObject.put(ExcelConst.WIDTH, 13*256);
        jsonObject.put(ExcelConst.TYPE, "1");
        headList.add(jsonObject);

        jsonObject = new JSONObject();
        jsonObject.put(ExcelConst.TITLE, "事业部");
        jsonObject.put(ExcelConst.WIDTH, 13*256);
        jsonObject.put(ExcelConst.TYPE, "1");
        headList.add(jsonObject);

        jsonObject = new JSONObject();
        jsonObject.put(ExcelConst.TITLE, "产品编号");
        jsonObject.put(ExcelConst.WIDTH, 13*256);
        jsonObject.put(ExcelConst.TYPE, "1");
        headList.add(jsonObject);


        jsonObject = new JSONObject();
        jsonObject.put(ExcelConst.TITLE, "中文品名");
        jsonObject.put(ExcelConst.WIDTH, 21*256);
        jsonObject.put(ExcelConst.TYPE, "1");
        headList.add(jsonObject);

        jsonObject = new JSONObject();
        jsonObject.put(ExcelConst.TITLE, "出货数量");
        jsonObject.put(ExcelConst.WIDTH, 11*256);
        jsonObject.put(ExcelConst.TYPE, "2");
        jsonObject.put(ExcelConst.FORMAT, "#,#0");

       // jsonObject.put(ExcelConst.TYPE, "1");
        //jsonObject.put(ExcelConst.FORMAT, "");
        headList.add(jsonObject);
        jsonObject = new JSONObject();
        jsonObject.put(ExcelConst.TITLE, "报关金额$");
        jsonObject.put(ExcelConst.WIDTH, 15*256);
        jsonObject.put(ExcelConst.TYPE, "2");
        jsonObject.put(ExcelConst.FORMAT, "#,#0.00");
        headList.add(jsonObject);

        jsonObject = new JSONObject();
        jsonObject.put(ExcelConst.TITLE, "提成金额$");
        jsonObject.put(ExcelConst.WIDTH, 15*256);
        jsonObject.put(ExcelConst.TYPE, "2");
        jsonObject.put(ExcelConst.FORMAT, "#,#0.00");
        headList.add(jsonObject);

        for (int i = 0; i < dataList.size(); i++) {
            //if (StringUtils.isEmpty(dataList.get(i).getPhoto())) continue;
            List<String> list = new ArrayList<>();


            BASE64Encoder base64Encoder = new BASE64Encoder();
            if (!StringUtils.isEmpty(dataList.get(i).getPhoto()))
                list.add( base64Encoder.encode(dataList.get(i).getPhoto()));
            else
                list.add( base64Encoder.encode("empty".getBytes()));

            list.add(dataList.get(i).getBuname());
            list.add(dataList.get(i).getItemno());
            list.add(dataList.get(i).getCHNItemName());

            //list.add(dataList.get(i).getENGItemName());
            list.add(String.valueOf(dataList.get(i).getShippingQty()));
            list.add(String.valueOf(dataList.get(i).getCustomsAccount()));
            list.add(String.valueOf(dataList.get(i).getTcje()));
            lists.add(list);
        }
        //System.out.println(lists);

        ExcelUtil.getExcel("d:\\2023年玻璃杯出货汇总.xlsx", lists, headList,true);


    }


    @Test
    public void grass_export_detail() throws  Exception{
        String fromrq="2022-01-01";
        String torq="2023-12-31";
        String supplierno="AHCZ003";
        List<GlassShipments_detail>  dataList=quotationsLineService.grass_ship_find_detail(fromrq,torq,supplierno);
        //System.out.println(dataList.size());
        List<JSONObject> headList = new ArrayList<>();


        List<List<String>> lists = new ArrayList<>();
        JSONObject jsonObject = new JSONObject();
        jsonObject.put(ExcelConst.TITLE, "图片");
        jsonObject.put(ExcelConst.WIDTH, 13*256);
        jsonObject.put(ExcelConst.TYPE, "1");
        headList.add(jsonObject);

        jsonObject = new JSONObject();
        jsonObject.put(ExcelConst.TITLE, "事业部");
        jsonObject.put(ExcelConst.WIDTH, 13*256);
        jsonObject.put(ExcelConst.TYPE, "1");
        headList.add(jsonObject);

        jsonObject = new JSONObject();
        jsonObject.put(ExcelConst.TITLE, "发票号码");
        jsonObject.put(ExcelConst.WIDTH, 13*256);
        jsonObject.put(ExcelConst.TYPE, "1");
        headList.add(jsonObject);

        jsonObject = new JSONObject();
        jsonObject.put(ExcelConst.TITLE, "出运日期");
        jsonObject.put(ExcelConst.WIDTH, 13*256);
        jsonObject.put(ExcelConst.TYPE, "1");
        headList.add(jsonObject);

        jsonObject = new JSONObject();
        jsonObject.put(ExcelConst.TITLE, "产品编号");
        jsonObject.put(ExcelConst.WIDTH, 13*256);
        jsonObject.put(ExcelConst.TYPE, "1");
        headList.add(jsonObject);


        jsonObject = new JSONObject();
        jsonObject.put(ExcelConst.TITLE, "中文品名");
        jsonObject.put(ExcelConst.WIDTH, 21*256);
        jsonObject.put(ExcelConst.TYPE, "1");
        headList.add(jsonObject);

        jsonObject = new JSONObject();
        jsonObject.put(ExcelConst.TITLE, "报关品名");
        jsonObject.put(ExcelConst.WIDTH, 21*256);
        jsonObject.put(ExcelConst.TYPE, "1");
        headList.add(jsonObject);

        jsonObject = new JSONObject();
        jsonObject.put(ExcelConst.TITLE, "开发人员");
        jsonObject.put(ExcelConst.WIDTH, 13*256);
        jsonObject.put(ExcelConst.TYPE, "1");
        headList.add(jsonObject);

        jsonObject = new JSONObject();
        jsonObject.put(ExcelConst.TITLE, "出货数量");
        jsonObject.put(ExcelConst.WIDTH, 11*256);

        jsonObject.put(ExcelConst.TYPE, "2");
        jsonObject.put(ExcelConst.FORMAT, "#,#0");
        //jsonObject.put(ExcelConst.FORMAT, "");
        headList.add(jsonObject);
        jsonObject = new JSONObject();
        jsonObject.put(ExcelConst.TITLE, "报关金额$");
        jsonObject.put(ExcelConst.WIDTH, 15*256);
        jsonObject.put(ExcelConst.TYPE, "2");
        jsonObject.put(ExcelConst.FORMAT, "#,#0.00");
        headList.add(jsonObject);

        jsonObject = new JSONObject();
        jsonObject.put(ExcelConst.TITLE, "提成点数");
        jsonObject.put(ExcelConst.WIDTH, 15*256);
        jsonObject.put(ExcelConst.TYPE, "2");
        jsonObject.put(ExcelConst.FORMAT, "#,#0.00");
        headList.add(jsonObject);

        jsonObject = new JSONObject();
        jsonObject.put(ExcelConst.TITLE, "提成金额$");
        jsonObject.put(ExcelConst.WIDTH, 15*256);
        jsonObject.put(ExcelConst.TYPE, "2");
        jsonObject.put(ExcelConst.FORMAT, "#,#0.00");
        headList.add(jsonObject);
        jsonObject = new JSONObject();
        jsonObject.put(ExcelConst.TITLE, "订单产品卡号");
        jsonObject.put(ExcelConst.WIDTH, 13*256);
        jsonObject.put(ExcelConst.TYPE, "1");
        headList.add(jsonObject);

        jsonObject = new JSONObject();
        jsonObject.put(ExcelConst.TITLE, "订单产品利润");
        jsonObject.put(ExcelConst.WIDTH, 13*256);
        jsonObject.put(ExcelConst.TYPE, "2");
        jsonObject.put(ExcelConst.FORMAT, "#,#0.00");
        headList.add(jsonObject);
        jsonObject = new JSONObject();
        jsonObject.put(ExcelConst.TITLE, "公司产品提成点数");
        jsonObject.put(ExcelConst.WIDTH, 13*256);
        jsonObject.put(ExcelConst.TYPE, "2");
        jsonObject.put(ExcelConst.FORMAT, "#,#0");
        headList.add(jsonObject);

        jsonObject = new JSONObject();
        jsonObject.put(ExcelConst.TITLE, "公司产品提成有效期");
        jsonObject.put(ExcelConst.WIDTH, 13*256);
        jsonObject.put(ExcelConst.TYPE, "1");

        headList.add(jsonObject);

        for (int i = 0; i < dataList.size(); i++) {
            //if (StringUtils.isEmpty(dataList.get(i).getPhoto())) continue;
            List<String> list = new ArrayList<>();


            BASE64Encoder base64Encoder = new BASE64Encoder();
           if (!StringUtils.isEmpty(dataList.get(i).getPhoto()))
                list.add( base64Encoder.encode(dataList.get(i).getPhoto()));
            else
                list.add( base64Encoder.encode("empty".getBytes()));

            list.add(dataList.get(i).getBuname());
            list.add(dataList.get(i).getInvoiceno());
            list.add(DateUtil.formatDate(dataList.get(i).getShippingdate()));
            list.add(dataList.get(i).getItemno());
            list.add(dataList.get(i).getCHNItemName());
            list.add(dataList.get(i).getCustomsCHNName());

            list.add(dataList.get(i).getCreator());

            //list.add(dataList.get(i).getENGItemName());
            list.add(String.valueOf(dataList.get(i).getShippingQty()));
            list.add(String.valueOf(dataList.get(i).getCustomsAccount()));
            list.add(String.valueOf(dataList.get(i).getTcds()));
            list.add(String.valueOf(dataList.get(i).getTcje()));
            list.add(dataList.get(i).getNegotiationNo());
            list.add(String.valueOf(dataList.get(i).getProfitrate()));
            list.add(String.valueOf(dataList.get(i).getItemstcds()));
            if (!StringUtils.isEmpty(dataList.get(i).getItemstcyxq()))
                list.add(DateUtil.formatDate(dataList.get(i).getItemstcyxq()));
            else
                list.add("");
            lists.add(list);
        }
        //System.out.println(lists);

        ExcelUtil.getExcel("d:\\2023年德力出货明细.xlsx", lists, headList,true);


    }
    @Test
    public void test_proudctimage() throws IOException {
        String itemno = "F010119H30";
        List<Productphoto> dataList = quotationsLineService.getproductandimage(itemno);
        System.out.println(dataList);
        List<JSONObject> headList = new ArrayList<>();


        List<List<String>> lists = new ArrayList<>();
        JSONObject jsonObject = new JSONObject();

        jsonObject = new JSONObject();
        jsonObject.put(ExcelConst.TITLE, "产品编号");
        jsonObject.put(ExcelConst.WIDTH, 13 * 256);
        jsonObject.put(ExcelConst.TYPE, "1");
        headList.add(jsonObject);

        jsonObject = new JSONObject();

        jsonObject.put(ExcelConst.TITLE, "图片");
        jsonObject.put(ExcelConst.WIDTH, 13 * 256);
        jsonObject.put(ExcelConst.TYPE, "1");
        headList.add(jsonObject);
        for (int i = 0; i < dataList.size(); i++) {
            //if (StringUtils.isEmpty(dataList.get(i).getPhoto())) continue;
            List<String> list = new ArrayList<>();

            list.add(dataList.get(i).getItemNo());
            BASE64Encoder base64Encoder = new BASE64Encoder();
            if (!StringUtils.isEmpty(dataList.get(i).getPhoto()))
                list.add(base64Encoder.encode(dataList.get(i).getPhoto()));
            else
                list.add(base64Encoder.encode("empty".getBytes()));

            lists.add(list);

        }
        ExcelUtil.getExcel("d:\\库存产品导出.xlsx", lists, headList,true);


    }
    @Test
    public void lvproduct_shipment() throws  Exception {
        List<LuProductShipments> dataList=quotationsLineService.Lvproduct_shipList();
        System.out.println(dataList.size());
        List<JSONObject> headList = new ArrayList<>();


        List<List<String>> lists = new ArrayList<>();
        JSONObject jsonObject = new JSONObject();
        jsonObject.put(ExcelConst.TITLE, "图片");
        jsonObject.put(ExcelConst.WIDTH, 13*256);
        jsonObject.put(ExcelConst.TYPE, "1");
        headList.add(jsonObject);

        jsonObject = new JSONObject();
        jsonObject.put(ExcelConst.TITLE, "发票号码");
        jsonObject.put(ExcelConst.WIDTH, 13*256);
        jsonObject.put(ExcelConst.TYPE, "1");
        headList.add(jsonObject);

        jsonObject = new JSONObject();
        jsonObject.put(ExcelConst.TITLE, "出运日期");
        jsonObject.put(ExcelConst.WIDTH, 13*256);
        jsonObject.put(ExcelConst.TYPE, "1");
        headList.add(jsonObject);

        jsonObject = new JSONObject();
        jsonObject.put(ExcelConst.TITLE, "币种");
        jsonObject.put(ExcelConst.WIDTH, 13*256);
        jsonObject.put(ExcelConst.TYPE, "1");
        headList.add(jsonObject);

        jsonObject = new JSONObject();
        jsonObject.put(ExcelConst.TITLE, "产品编号");
        jsonObject.put(ExcelConst.WIDTH, 13*256);
        jsonObject.put(ExcelConst.TYPE, "1");
        headList.add(jsonObject);

        jsonObject = new JSONObject();
        jsonObject.put(ExcelConst.TITLE, "海关编码");
        jsonObject.put(ExcelConst.WIDTH, 13*256);
        jsonObject.put(ExcelConst.TYPE, "1");
        headList.add(jsonObject);


        jsonObject = new JSONObject();
        jsonObject.put(ExcelConst.TITLE, "中文品名");
        jsonObject.put(ExcelConst.WIDTH, 21*256);
        jsonObject.put(ExcelConst.TYPE, "1");
        headList.add(jsonObject);

        jsonObject = new JSONObject();
        jsonObject.put(ExcelConst.TITLE, "英文品名");
        jsonObject.put(ExcelConst.WIDTH, 21*256);
        jsonObject.put(ExcelConst.TYPE, "1");
        headList.add(jsonObject);

        jsonObject = new JSONObject();
        jsonObject.put(ExcelConst.TITLE, "报关中文品名");
        jsonObject.put(ExcelConst.WIDTH, 21*256);
        jsonObject.put(ExcelConst.TYPE, "1");
        headList.add(jsonObject);

        jsonObject = new JSONObject();
        jsonObject.put(ExcelConst.TITLE, "报关英文品名");
        jsonObject.put(ExcelConst.WIDTH, 21*256);
        jsonObject.put(ExcelConst.TYPE, "1");
        headList.add(jsonObject);




        jsonObject = new JSONObject();
        jsonObject.put(ExcelConst.TITLE, "出货数量");
        jsonObject.put(ExcelConst.WIDTH, 11*256);

        jsonObject.put(ExcelConst.TYPE, "2");
        jsonObject.put(ExcelConst.FORMAT, "#,#0");
        //jsonObject.put(ExcelConst.FORMAT, "");
        headList.add(jsonObject);
        jsonObject = new JSONObject();
        jsonObject.put(ExcelConst.TITLE, "报关金额");
        jsonObject.put(ExcelConst.WIDTH, 15*256);
        jsonObject.put(ExcelConst.TYPE, "2");
        jsonObject.put(ExcelConst.FORMAT, "#,#0.00");
        headList.add(jsonObject);








        for (int i = 0; i < dataList.size(); i++) {
            //if (StringUtils.isEmpty(dataList.get(i).getPhoto())) continue;
            List<String> list = new ArrayList<>();


            BASE64Encoder base64Encoder = new BASE64Encoder();
            if (!StringUtils.isEmpty(dataList.get(i).getPhoto()))
                list.add( base64Encoder.encode(dataList.get(i).getPhoto()));
            else
                list.add( base64Encoder.encode("empty".getBytes()));

            list.add(dataList.get(i).getInvoiceno());

            list.add(DateUtil.formatDate(dataList.get(i).getShippingDate()));
            list.add(dataList.get(i).getCurrency());
            list.add(dataList.get(i).getItemNo());
            list.add(dataList.get(i).getHSCode());
            list.add(dataList.get(i).getCHNItemName());
            list.add(dataList.get(i).getENGItemName());
            list.add(dataList.get(i).getBillName());
            list.add(dataList.get(i).getCustomsENGName());



            //list.add(dataList.get(i).getENGItemName());
            list.add(String.valueOf(dataList.get(i).getShippingQty()));
            list.add(String.valueOf(dataList.get(i).getCustomsAccount()));


            lists.add(list);
        }
        //System.out.println(lists);

        ExcelUtil.getExcel("d:\\近三年海关编码961510的出货情况.xlsx", lists, headList,true);


    }

    @Test
    public void grass_zsdd_detail() throws  Exception {
//        String fromrq="";
//        String torq="2023-12-31";
//        String supplierno="AHCZ003";g
        List<GlassShipments_detail> dataList = quotationsLineService.dl_zsdd_detail();
//        for (int i = 0; i < dataList.size(); i++) {
//            System.out.println(dataList.get(i).getItemno());
//        }
        List<JSONObject> headList = new ArrayList<>();


        List<List<String>> lists = new ArrayList<>();
        JSONObject jsonObject = new JSONObject();
        jsonObject.put(ExcelConst.TITLE, "图片");
        jsonObject.put(ExcelConst.WIDTH, 13*256);
        jsonObject.put(ExcelConst.TYPE, "1");
        headList.add(jsonObject);

        jsonObject = new JSONObject();
        jsonObject.put(ExcelConst.TITLE, "事业部");
        jsonObject.put(ExcelConst.WIDTH, 13*256);
        jsonObject.put(ExcelConst.TYPE, "1");
        headList.add(jsonObject);

        jsonObject = new JSONObject();
        jsonObject.put(ExcelConst.TITLE, "外销合同");
        jsonObject.put(ExcelConst.WIDTH, 13*256);
        jsonObject.put(ExcelConst.TYPE, "1");
        headList.add(jsonObject);

        jsonObject = new JSONObject();
        jsonObject.put(ExcelConst.TITLE, "合同日期");
        jsonObject.put(ExcelConst.WIDTH, 13*256);
        jsonObject.put(ExcelConst.TYPE, "1");
        headList.add(jsonObject);

        jsonObject = new JSONObject();
        jsonObject.put(ExcelConst.TITLE, "产品编号");
        jsonObject.put(ExcelConst.WIDTH, 13*256);
        jsonObject.put(ExcelConst.TYPE, "1");
        headList.add(jsonObject);


        jsonObject = new JSONObject();
        jsonObject.put(ExcelConst.TITLE, "中文品名");
        jsonObject.put(ExcelConst.WIDTH, 21*256);
        jsonObject.put(ExcelConst.TYPE, "1");
        headList.add(jsonObject);



        jsonObject = new JSONObject();
        jsonObject.put(ExcelConst.TITLE, "开发人员");
        jsonObject.put(ExcelConst.WIDTH, 13*256);
        jsonObject.put(ExcelConst.TYPE, "1");
        headList.add(jsonObject);

        jsonObject = new JSONObject();
        jsonObject.put(ExcelConst.TITLE, "合同数量");
        jsonObject.put(ExcelConst.WIDTH, 11*256);

        jsonObject.put(ExcelConst.TYPE, "2");
        jsonObject.put(ExcelConst.FORMAT, "#,#0");
        //jsonObject.put(ExcelConst.FORMAT, "");
        headList.add(jsonObject);
        jsonObject = new JSONObject();
        jsonObject.put(ExcelConst.TITLE, "外销金额$");
        jsonObject.put(ExcelConst.WIDTH, 15*256);
        jsonObject.put(ExcelConst.TYPE, "2");
        jsonObject.put(ExcelConst.FORMAT, "#,#0.00");
        headList.add(jsonObject);

        jsonObject = new JSONObject();
        jsonObject.put(ExcelConst.TITLE, "提成点数");
        jsonObject.put(ExcelConst.WIDTH, 13*256);
        jsonObject.put(ExcelConst.TYPE, "2");
        jsonObject.put(ExcelConst.FORMAT, "#,#0.00");
        headList.add(jsonObject);

        jsonObject = new JSONObject();
        jsonObject.put(ExcelConst.TITLE, "提成金额$");
        jsonObject.put(ExcelConst.WIDTH, 15*256);
        jsonObject.put(ExcelConst.TYPE, "2");
        jsonObject.put(ExcelConst.FORMAT, "#,#0.00");
        headList.add(jsonObject);

        jsonObject = new JSONObject();
        jsonObject.put(ExcelConst.TITLE, "订单产品卡号");
        jsonObject.put(ExcelConst.WIDTH, 13*256);
        jsonObject.put(ExcelConst.TYPE, "1");
        headList.add(jsonObject);

        jsonObject = new JSONObject();
        jsonObject.put(ExcelConst.TITLE, "订单产品利润");
        jsonObject.put(ExcelConst.WIDTH, 13*256);
        jsonObject.put(ExcelConst.TYPE, "2");
        jsonObject.put(ExcelConst.FORMAT, "#,#0.00");
        headList.add(jsonObject);
        jsonObject = new JSONObject();
        jsonObject.put(ExcelConst.TITLE, "提成点数");
        jsonObject.put(ExcelConst.WIDTH, 13*256);
        jsonObject.put(ExcelConst.TYPE, "2");
        jsonObject.put(ExcelConst.FORMAT, "#,#0");
        headList.add(jsonObject);

        jsonObject = new JSONObject();
        jsonObject.put(ExcelConst.TITLE, "提成有效期");
        jsonObject.put(ExcelConst.WIDTH, 13*256);
        jsonObject.put(ExcelConst.TYPE, "1");

        headList.add(jsonObject);

        for (int i = 0; i < dataList.size(); i++) {
            //if (StringUtils.isEmpty(dataList.get(i).getPhoto())) continue;
            List<String> list = new ArrayList<>();


            BASE64Encoder base64Encoder = new BASE64Encoder();
            if (!StringUtils.isEmpty(dataList.get(i).getPhoto()))
                list.add( base64Encoder.encode(dataList.get(i).getPhoto()));
            else
                list.add( base64Encoder.encode("empty".getBytes()));

            list.add(dataList.get(i).getBuname());
            list.add(dataList.get(i).getInvoiceno());
            list.add(DateUtil.formatDate(dataList.get(i).getShippingdate()));
            list.add(dataList.get(i).getItemno());
            list.add(dataList.get(i).getCHNItemName());
            //list.add(dataList.get(i).getCustomsCHNName());

            list.add(dataList.get(i).getCreator());

            //list.add(dataList.get(i).getENGItemName());
            list.add(String.valueOf(dataList.get(i).getShippingQty()));
            list.add(String.valueOf(dataList.get(i).getCustomsAccount()));
            list.add(String.valueOf(dataList.get(i).getTcds()));
            list.add(String.valueOf(dataList.get(i).getTcje()));
            list.add(dataList.get(i).getNegotiationNo());
            list.add(String.valueOf(dataList.get(i).getProfitrate()));
            list.add(String.valueOf(dataList.get(i).getItemstcds()));
            if (!StringUtils.isEmpty(dataList.get(i).getItemstcyxq()))
            list.add(DateUtil.formatDate(dataList.get(i).getItemstcyxq()));
            else
                list.add("");

            lists.add(list);
        }
        //System.out.println(lists);

        ExcelUtil.getExcel("d:\\2023年德力在手订单详情.xlsx", lists, headList,true);


    }







    @Test
    public void hot_selling_item_export() throws  Exception{

//            QueryWrapper<QuotationsLine> wrapper=new QueryWrapper<>();
//            //wrapper.select("Photo", "ItemNo", "CHNItemName","CHNSpecification","OrderQty","SalesAmount")
//            wrapper
//                    .eq("parentid","18862288-6114-4EB1-A129-0321A48CB744")
//                    .isNotNull("photo");
//
//                    //.orderByDesc("created");
//
//            List<QuotationsLine>  dataList=quotationsLineService.list(wrapper);
        String fromrq="2022-01-01";
        String torq="2022-12-31";
        List<QuotationsLine>  dataList=quotationsLineService.getsell_well_items(fromrq,torq);
        System.out.println(dataList.size());
            //System.exit(0);
            List<JSONObject> headList = new ArrayList<>();


            List<List<String>> lists = new ArrayList<>();
            JSONObject jsonObject = new JSONObject();
            jsonObject.put(ExcelConst.TITLE, "图片");
            jsonObject.put(ExcelConst.WIDTH, 13*256);
            jsonObject.put(ExcelConst.TYPE, "1");
            headList.add(jsonObject);

            jsonObject = new JSONObject();
            jsonObject.put(ExcelConst.TITLE, "产品编号");
            jsonObject.put(ExcelConst.WIDTH, 13*256);
            jsonObject.put(ExcelConst.TYPE, "1");
            headList.add(jsonObject);


            jsonObject = new JSONObject();
            jsonObject.put(ExcelConst.TITLE, "中文品名");
            jsonObject.put(ExcelConst.WIDTH, 21*256);
            jsonObject.put(ExcelConst.TYPE, "1");
            headList.add(jsonObject);
            jsonObject = new JSONObject();
            jsonObject.put(ExcelConst.TITLE, "产品规格");
            jsonObject.put(ExcelConst.WIDTH, 45*256);
            jsonObject.put(ExcelConst.TYPE, "1");
            headList.add(jsonObject);

            jsonObject = new JSONObject();
            jsonObject.put(ExcelConst.TITLE, "下单数量");
            jsonObject.put(ExcelConst.WIDTH, 11*256);

            jsonObject.put(ExcelConst.TYPE, "1");
            //jsonObject.put(ExcelConst.FORMAT, "");
            headList.add(jsonObject);
            jsonObject = new JSONObject();
            jsonObject.put(ExcelConst.TITLE, "下单金额");
            jsonObject.put(ExcelConst.WIDTH, 17*256);
            jsonObject.put(ExcelConst.TYPE, "2");
            jsonObject.put(ExcelConst.FORMAT, "#,#0.00");
            headList.add(jsonObject);

            for (int i = 0; i < dataList.size(); i++) {
                //if (StringUtils.isEmpty(dataList.get(i).getPhoto())) continue;
                List<String> list = new ArrayList<>();


                BASE64Encoder base64Encoder = new BASE64Encoder();
                if (!StringUtils.isEmpty(dataList.get(i).getPhoto()))
                list.add( base64Encoder.encode(dataList.get(i).getPhoto()));
                else
                    list.add( base64Encoder.encode("empty".getBytes()));

                list.add(dataList.get(i).getItemNo());
                list.add(dataList.get(i).getCHNItemName());

                //list.add(dataList.get(i).getENGItemName());
                list.add(dataList.get(i).getCHNSpecification());
                list.add(String.valueOf(dataList.get(i).getOrderQty()));
                list.add(String.valueOf(dataList.get(i).getSalesAmount()));
                lists.add(list);
            }
            //System.out.println(lists);

            ExcelUtil.getExcel("d:\\11.xlsx", lists, headList,true);


        }




    @Test
    public void test_item() throws  Exception{
        QueryWrapper<Items> wrapper=new QueryWrapper<>();
        wrapper.select("top 1000 *")
                .orderByDesc("created");

        List<Items>  dataList=itemsService.list(wrapper);
        List<JSONObject> headList = new ArrayList<>();


        List<List<String>> lists = new ArrayList<>();
        JSONObject jsonObject = new JSONObject();
        jsonObject.put(ExcelConst.TITLE, "图片");
        jsonObject.put(ExcelConst.WIDTH, 3000);
        jsonObject.put(ExcelConst.TYPE, "1");
        headList.add(jsonObject);

         jsonObject = new JSONObject();
        jsonObject.put(ExcelConst.TITLE, "产品编号");
        jsonObject.put(ExcelConst.WIDTH, 3000);
        jsonObject.put(ExcelConst.TYPE, "1");
        headList.add(jsonObject);


        jsonObject = new JSONObject();
        jsonObject.put(ExcelConst.TITLE, "中文品名");
        jsonObject.put(ExcelConst.WIDTH, 6500);
        jsonObject.put(ExcelConst.TYPE, "1");
        headList.add(jsonObject);
        jsonObject = new JSONObject();
        jsonObject.put(ExcelConst.TITLE, "英文品名");
        jsonObject.put(ExcelConst.WIDTH, 6500);
        jsonObject.put(ExcelConst.TYPE, "1");
        headList.add(jsonObject);
        jsonObject = new JSONObject();
        jsonObject.put(ExcelConst.TITLE, "产品规格");
        jsonObject.put(ExcelConst.WIDTH, 6500);
        jsonObject.put(ExcelConst.TYPE, "1");
        headList.add(jsonObject);
        jsonObject = new JSONObject();
        jsonObject.put(ExcelConst.TITLE, "外销单价");
        jsonObject.put(ExcelConst.WIDTH, 2500);

        jsonObject.put(ExcelConst.TYPE, "2");
        jsonObject.put(ExcelConst.FORMAT, "#,#0.00");
        headList.add(jsonObject);
        jsonObject = new JSONObject();
        jsonObject.put(ExcelConst.TITLE, "采购单价");
        jsonObject.put(ExcelConst.WIDTH, 2500);
        jsonObject.put(ExcelConst.TYPE, "2");
        jsonObject.put(ExcelConst.FORMAT, "#,#0.00");
        headList.add(jsonObject);

        for (int i = 0; i < dataList.size(); i++) {
            List<String> list = new ArrayList<>();
            BASE64Encoder base64Encoder = new BASE64Encoder();
            list.add( base64Encoder.encode(dataList.get(i).getPhoto()));

            list.add(dataList.get(i).getItemNo());
            list.add(dataList.get(i).getCHNItemName());

            list.add(dataList.get(i).getENGItemName());
            list.add(dataList.get(i).getCHNSpecification());
            list.add(String.valueOf(dataList.get(i).getSalesPrice()));
            list.add(String.valueOf(dataList.get(i).getPurchasePrice()));
            lists.add(list);
        }
        //System.out.println(lists);

        ExcelUtil.getExcel("d:\\1.xlsx", lists, headList,true);


    }




}
